package com.ums.scumspay.bigdataimport.QryFile;

import com.ums.scumspay.bigdataimport.Util.DoubleUtil;
import com.ums.scumspay.bigdataimport.constant.FilePathConstant;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;

import javax.annotation.PostConstruct;
import java.io.*;
import java.time.LocalDate;
import java.time.Month;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Objects;

public class KpiDataService {
    /**
     * 作者 LYC
     * 时间 2022/9/5
     * 参数 [filepath]
     * 返回值 void
     *方法描述：读取KPI数据
     **/
    public static HashMap<String, ArrayList<ArrayList<String>>> readFile(String filepath){
        HashMap<String, ArrayList<ArrayList<String>>> map=ExecUtil.readExcel(new File(filepath),1);
        return  map;
        //  return null;
    }


    /**
     * 作者 LYC
     * 时间 2022/9/5
     * 参数 [list]
     * 返回值 void
     *方法描述：读取表1.新拓展优质商户收单净收益奖励明细表
     **/
    public static void readTable1(ArrayList<ArrayList<String>> list, FileOutputStream out, String month)throws IOException {
        out.write(("--table1\r\n").getBytes());
        for (int i = 0; i < list.size(); i++) {
            ArrayList<String> array = list.get(i);
            System.out.println("INSERT INTO KPI_TABLE1(STAFF_NAME,PROFIT,BILIE,COMMISSION,TERM_NUMBER,DEPRECIATION,MONTH) " +
                    "VALUES('"+array.get(0)+"','"+ DoubleUtil.round_toformat(Double.parseDouble(array.get(1)),2) +"'," +
                    "'"+array.get(2)+"','"+ DoubleUtil.round_toformat(Double.parseDouble(array.get(3)),2) +"'," +
                    "'"+array.get(4)+"','"+DoubleUtil.round_toformat(Double.parseDouble(array.get(5)),2)+"','"+month+"');");
            out.write(("INSERT INTO KPI_TABLE1(STAFF_NAME,PROFIT,BILIE,COMMISSION,TERM_NUMBER,DEPRECIATION,MONTH) " +
                                        "VALUES('"+array.get(0)+"','"+ DoubleUtil.round_toformat(Double.parseDouble(array.get(1)),2) +"'," +
                                        "'"+array.get(2)+"','"+ DoubleUtil.round_toformat(Double.parseDouble(array.get(3)),2) +"'," +
                                        "'"+array.get(4)+"','"+DoubleUtil.round_toformat(Double.parseDouble(array.get(5)),2)+"','"+month+"');" + "\r\n").getBytes());

        }
        //out.close();
    }
    /**
     * 作者 LYC
     * 时间 2022/9/5
     * 参数 [list]
     * 返回值 void
     *方法描述：表2.新拓展线上收单商户收单净收益奖励明细表
     **/
    public static void readTable2(ArrayList<ArrayList<String>> list, FileOutputStream out, String month)throws IOException{
        out.write(("--table2\r\n").getBytes());
        for (int i = 0; i < list.size(); i++) {
            ArrayList<String> array = list.get(i);
            
            System.out.println("INSERT INTO KPI_TABLE2(STAFF_NAME,PROFIT,BILIE,COMMISSION,MONTH) " +
                    "VALUES('"+array.get(0)+"','"+ DoubleUtil.round_toformat(Double.parseDouble(array.get(1)),2) +"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(2)),2)+"','"+ DoubleUtil.round_toformat(Double.parseDouble(array.get(3)),2) +"','"+month+"');");
            out.write(("INSERT INTO KPI_TABLE2(STAFF_NAME,PROFIT,BILIE,COMMISSION,MONTH) " +
                    "VALUES('"+array.get(0)+"','"+ DoubleUtil.round_toformat(Double.parseDouble(array.get(1)),2) +"'," +
                    "'"+array.get(2)+"','"+ DoubleUtil.round_toformat(Double.parseDouble(array.get(3)),2) +"','"+month+"');" + "\r\n").getBytes());

        }
        //out.close();
    }
    /**
     * 作者 LYC
     * 时间 2022/9/5
     * 参数 [list]
     * 返回值 void
     *方法描述：表3.存量计提净收单收益商户收单净收益奖励明细表
     **/
    public static void readTable3(ArrayList<ArrayList<String>> list, FileOutputStream out, String month)throws IOException{
        out.write(("--table3\r\n").getBytes());
        for (int i = 0; i < list.size(); i++) {
            ArrayList<String> array = list.get(i);
            
            System.out.println("INSERT INTO KPI_TABLE3(STAFF_NAME,PROFIT,BILIE,COMMISSION,PROFIT1,BILIE1,COMMISSION1,PROFIT_SUM,COMMISSION_SUM,TERM_NUMBER,MONTH) " +
                    "VALUES('"+array.get(0)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(1)),2)+"'," +
                    "'"+array.get(2)+"','"+DoubleUtil.round_toformat(Double.parseDouble(array.get(3)),2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(1)),2)+"'," +"'0.2'"+
                    ",'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(3)),2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(5)),2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(6)),2)+"'," +
                    "'"+array.get(4)+"','"+month+"');");
            out.write(("INSERT INTO KPI_TABLE3(STAFF_NAME,PROFIT,BILIE,COMMISSION,PROFIT1,BILIE1,COMMISSION1,PROFIT_SUM,COMMISSION_SUM,TERM_NUMBER,MONTH) " +
                    "VALUES('"+array.get(0)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(1)),2)+"'," +
                    "'"+array.get(2)+"','"+DoubleUtil.round_toformat(Double.parseDouble(array.get(3)),2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(1)),2)+"'," +"'0.2'"+
                    ",'"+array.get(3)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(5)),2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(6)),2)+"'," +
                    "'"+array.get(4)+"','"+month+"');"
                    +"\r\n").getBytes());;
        }
        //out.close();
    }
    /**
     * 作者 LYC
     * 时间 2022/9/5
     * 参数 [list]
     * 返回值 void
     *方法描述：表4.每个客户经理当月收单净收益-7月
     **/
    public static void readTable4(ArrayList<ArrayList<String>> list, FileOutputStream out, String month)throws IOException{
        out.write(("--table4\r\n").getBytes());
        for (int i = 0; i < list.size(); i++) {
            ArrayList<String> array = list.get(i);
            System.out.println("INSERT INTO KPI_TABLE4(STAFF_NAME,PROFIT,T_TERM_NUMBER,M_TERM_NUMBER,TERM_SUM,DEPRECIATION,MONTH) " +
                    "VALUES('"+array.get(0)+"','"+DoubleUtil.round_toformat(Double.parseDouble(array.get(1)),2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(2)),2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(3)),2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(4)),2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(5)),2)+"','"+month+"');");
            out.write(("INSERT INTO KPI_TABLE4(STAFF_NAME,PROFIT,T_TERM_NUMBER,M_TERM_NUMBER,TERM_SUM,DEPRECIATION,MONTH) " +
                    "VALUES('"+array.get(0)+"','"+DoubleUtil.round_toformat(Double.parseDouble(array.get(1)),2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(2)),2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(3)),2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(4)),2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(5)),2)+"','"+month+"');"+"\r\n").getBytes());
        }
        //out.close();
    }
    /**
     * 作者 LYC
     * 时间 2022/9/5
     * 参数 [list]
     * 返回值 void
     *方法描述：表5.存量月均净收单收益
     **/
    public static void readTable5(ArrayList<ArrayList<String>> list, FileOutputStream out, String month)throws IOException{
        out.write(("--table5\r\n").getBytes());
        for (int i = 0; i < list.size(); i++) {
            ArrayList<String> array = list.get(i);
            System.out.println("INSERT INTO KPI_TABLE5(STAFF_NAME,PROFIT4,PROFIT6,PROFIT_SUM,TERM_NUMBER,DEPRECIATION,BILIE,MONTH) " +
                    "VALUES('"+array.get(0)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(1)),2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(2)),2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(3)),2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(4)),2)+"'," +
                    "'"+array.get(5)+"','0.7','"+month+"');");
            out.write(("INSERT INTO KPI_TABLE5(STAFF_NAME,PROFIT4,PROFIT6,PROFIT_SUM,TERM_NUMBER,DEPRECIATION,BILIE,MONTH) " +
                    "VALUES('"+array.get(0)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(1)),2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(2)),2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(3)),2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(4)),2)+"'," +
                    "'"+array.get(5)+"','0.7','"+month+"');"+"\r\n").getBytes());
        }
        //out.close();
    }

    /**
     * 作者 LYC
     * 时间 2022/9/6
     * 参数 [list]
     * 返回值 void
     *方法描述：表6.有效终端维护台数
     **/
    protected static void readTable6(ArrayList<ArrayList<String>> list,FileOutputStream out, String month)throws IOException{
    

    }
    /**
     * 作者 LYC
     * 时间 2022/9/6
     * 参数 [list]
     * 返回值 void
     *方法描述：表7.产品计价(服务费售卖开发费系统费小U系列等).xlsx
     **/
    public static void readTable7(ArrayList<ArrayList<String>> list, FileOutputStream out, String month)throws IOException{
        out.write(("--table7\r\n").getBytes());
        for (int i = 0; i < list.size(); i++) {
            ArrayList<String> array = list.get(i);
            
            System.out.println("INSERT INTO KPI_TABLE7(STAFF_NAME,AMOUNT,MONTH) " +
                    "VALUES('"+array.get(0)+"','"+DoubleUtil.round_toformat(Double.parseDouble(array.get(1)),2)+"','"+month+"');");
            out.write(("INSERT INTO KPI_TABLE7(STAFF_NAME,AMOUNT,MONTH) " +
                    "VALUES('"+array.get(0)+"','"+DoubleUtil.round_toformat(Double.parseDouble(array.get(1)),2)+"','"+month+"');"+"\r\n")
                    .getBytes());
        }
        //out.close();
    }
    /**
     * 作者 LYC
     * 时间 2022/9/6
     * 参数 [list]
     * 返回值 void
     *方法描述：表8营销联盟奖励
     **/
    public static void readTable8(ArrayList<ArrayList<String>> list, FileOutputStream out, String month)throws IOException{
        out.write(("--table8\r\n").getBytes());
        for (int i = 1; i < list.size(); i++) {
            ArrayList<String> array = list.get(i);

            System.out.println("INSERT INTO KPI_TABLE8(STAFF_NAME,AMOUNT,Z_AMOUNT,AMOUNT_SUM,MONTH) " +
                    "VALUES('"+array.get(0)+"','"+DoubleUtil.round_toformat(Double.parseDouble(array.get(1)),2)+"'," +
                    "'0.00'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(1)),2)+"','"+month+"');");
            out.write(("INSERT INTO KPI_TABLE8(STAFF_NAME,AMOUNT,Z_AMOUNT,AMOUNT_SUM,MONTH) " +
                    "VALUES('"+array.get(0)+"','"+DoubleUtil.round_toformat(Double.parseDouble(array.get(1)),2)+"'," +
                    "'0.00'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(1)),2)+"','"+month+"');"+"\r\n")
                    .getBytes());
        }
        //out.close();
    }
    /**
     * 作者 LYC
     * 时间 2022/9/6
     * 参数 [list]
     * 返回值 void
     *方法描述：表9.行业ERP、智慧园区
     **/
    public static void readTable9(ArrayList<ArrayList<String>> list, FileOutputStream out, String month)throws IOException{
        out.write(("--table9\r\n").getBytes());
        for (int i = 0; i < list.size(); i++) {
            ArrayList<String> array = list.get(i);

            System.out.println("INSERT INTO KPI_TABLE9(STAFF_NAME,AMOUNT,MONTH) VALUES('"+array.get(2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(3)),2)+"','"+month+"');");
            out.write(("INSERT INTO KPI_TABLE9(STAFF_NAME,AMOUNT,MONTH) VALUES('"+array.get(2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(3)),2)+"','"+month+"');"+"\r\n")
                    .getBytes());
        }
        //out.close();
    }
    /**
     * 作者 LYC
     * 时间 2022/9/6
     * 参数 [list]
     * 返回值 void
     *方法描述：表10-天天富融资理财
     **/
    public static void readTable10(ArrayList<ArrayList<String>> list, FileOutputStream out, String month)throws IOException{
        out.write(("--table10\r\n").getBytes());
        for (int i = 1; i < list.size(); i++) {
            ArrayList<String> array = list.get(i);

            System.out.println("INSERT INTO KPI_TABLE10(STAFF_NAME,TRANSLATE,VALUATION,REWARD_15,REWARD_2,AMOUNT,MONTH) " +
                    "VALUES('"+array.get(1)+"','"+DoubleUtil.round_toformat(Double.parseDouble(array.get(2)),2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(3)),2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(4)),2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(5)),2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(6)),2)+"','"+month+"');");
            out.write(("INSERT INTO KPI_TABLE10(STAFF_NAME,TRANSLATE,VALUATION,REWARD_15,REWARD_2,AMOUNT,MONTH) " +
                    "VALUES('"+array.get(1)+"','"+DoubleUtil.round_toformat(Double.parseDouble(array.get(2)),2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(3)),2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(4)),2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(5)),2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(6)),2)+"','"+month+"');"+"\r\n").getBytes());
        }
        //out.close();
    }
    /**
     * 作者 LYC
     * 时间 2022/9/6
     * 参数 [list]
     * 返回值 void
     *方法描述：表11.分期业务
     **/
    public static void readTable11(ArrayList<ArrayList<String>> list,FileOutputStream out, String month)throws IOException{
        out.write(("--table11\r\n").getBytes());
        for (int i = 0; i < list.size(); i++) {
            ArrayList<String> array = list.get(i);

            System.out.println("INSERT INTO KPI_TABLE11(STAFF_NAME,AMOUNT,MONTH) VALUES('"+array.get(1)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(2)),2)+"','"+month+"');");
            out.write(("INSERT INTO KPI_TABLE11(STAFF_NAME,AMOUNT,MONTH) VALUES('"+array.get(1)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(2)),2)+"','"+month+"');"+"\r\n").getBytes());
        }
        //out.close();
    }
    /**
     * 作者 LYC
     * 时间 2022/9/6
     * 参数 [list]
     * 返回值 void
     *方法描述：表12.宝信返佣
     **/
    public static void readTable12(ArrayList<ArrayList<String>> list, FileOutputStream out, String month)throws IOException{
        out.write(("--table12\r\n").getBytes());
        for (int i = 0; i < list.size(); i++) {
            ArrayList<String> array = list.get(i);

            System.out.println("INSERT INTO KPI_TABLE12(STAFF_NAME,AMOUNT,MONTH) VALUES('"+array.get(0)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(1)),2)+"','"+month+"');");
            out.write(("INSERT INTO KPI_TABLE12(STAFF_NAME,AMOUNT,MONTH) VALUES('"+array.get(0)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(1)),2)+"','"+month+"');"+"\r\n").getBytes());
        }
        //out.close();
    }
    /**
     * 作者 LYC
     * 时间 2022/9/6
     * 参数 [list]
     * 返回值 void
     *方法描述：表13.酒店行业产品
     **/
    public static void readTable13(ArrayList<ArrayList<String>> list,FileOutputStream out, String month)throws IOException{
        for (int i = 0; i < list.size(); i++) {
            ArrayList<String> array = list.get(i);

            System.out.println("INSERT INTO KPI_TABLE13(STAFF_NAME,AMOUNT,MONTH) VALUES('"+array.get(2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(3)),2)+"','"+month+"');");

            out.write(("INSERT INTO KPI_TABLE13(STAFF_NAME,AMOUNT,MONTH) VALUES('"+array.get(2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(3)),2)+"','"+month+"');"+"\r\n").getBytes());
        }
        //out.close();
    }
    /**
     * 作者 LYC
     * 时间 2022/9/6
     * 参数 [list]
     * 返回值 void
     *方法描述：表14.行缴
     **/
    public static void readTable14(ArrayList<ArrayList<String>> list, FileOutputStream out, String month)throws IOException{
        out.write(("--table14\r\n").getBytes());
        for (int i = 0; i < list.size(); i++) {
            ArrayList<String> array = list.get(i);

            System.out.println("INSERT INTO KPI_TABLE14(STAFF_NAME,AMOUNT,MONTH) VALUES('"+array.get(1)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(2)),2)+"','"+month+"');");

            out.write(("INSERT INTO KPI_TABLE14(STAFF_NAME,AMOUNT,MONTH) VALUES('"+array.get(1)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(2)),2)+"','"+month+"');"+"\r\n")
                    .getBytes());
        }
        //out.close();
    }
    public static void readTable15(ArrayList<ArrayList<String>> list,FileOutputStream out)throws IOException{

    }
    /**
     * 作者 LYC
     * 时间 2022/9/6
     * 参数 [list]
     * 返回值 void
     *方法描述：表16 梧桐加油站
     **/
    public static void readTable16(ArrayList<ArrayList<String>> list,FileOutputStream out, String month)throws IOException{
        for (int i = 0; i < list.size(); i++) {
            ArrayList<String> array = list.get(i);

            System.out.println("INSERT INTO KPI_TABLE16(STAFF_NAME,AMOUNT,MONTH) VALUES('"+array.get(2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(3)),2)+"','"+month+"');");

            out.write(("INSERT INTO KPI_TABLE16(STAFF_NAME,AMOUNT,MONTH) VALUES('"+array.get(2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(3)),2)+"','"+month+"');"+"\r\n")
                    .getBytes());
        }
        //out.close();
    }
    /**
     * 作者 LYC
     * 时间 2022/9/6
     * 参数 [list]
     * 返回值 void
     *方法描述：表17 新福农通
     **/
    public static void readTable17(ArrayList<ArrayList<String>> list,FileOutputStream out, String month)throws IOException{
        for (int i = 0; i < list.size(); i++) {
            ArrayList<String> array=list.get(i);
            System.out.println("INSERT INTO KPI_TABLE17(STAFF_NAME,AMOUNT,MONTH) VALUES('"+array.get(2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(3)),2)+"','"+month+"');");

            out.write(("INSERT INTO KPI_TABLE17(STAFF_NAME,AMOUNT,MONTH) VALUES('"+array.get(2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(3)),2)+"','"+month+"');"+"\r\n")
                    .getBytes());
        }
        //out.close();
    }
    /**
     * 作者 LYC
     * 时间 2022/9/6
     * 参数 [list]
     * 返回值 void
     *方法描述：表18.支付即会员、广告媒体
     **/
    public static void readTable18(ArrayList<ArrayList<String>> list,FileOutputStream out, String month)throws IOException{
        for (int i = 0; i < list.size(); i++) {
            ArrayList<String> array=list.get(i);
            System.out.println("INSERT INTO KPI_TABLE18(STAFF_NAME,AMOUNT,MONTH) VALUES('"+array.get(2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(3)),2)+"','"+month+"');");

            out.write(("INSERT INTO KPI_TABLE18(STAFF_NAME,AMOUNT,MONTH) VALUES('"+array.get(2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(3)),2)+"','"+month+"');"+"\r\n")
                    .getBytes());
        }
        //out.close();
    }

    /**
     * 作者 LYC
     * 时间 2022/9/6
     * 参数 [list]
     * 返回值 void
     *方法描述：表19.推广增值创新产品（首单）
     **/
    public static void readTable19(ArrayList<ArrayList<String>> list,FileOutputStream out, String month)throws IOException{
        for (int i = 0; i < list.size(); i++) {
            ArrayList<String> array=list.get(i);
            System.out.println("INSERT INTO KPI_TABLE19(STAFF_NAME,AMOUNT,MONTH) VALUES('"+array.get(2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(3)),2)+"','"+month+"');");

            out.write(("INSERT INTO KPI_TABLE19(STAFF_NAME,AMOUNT,MONTH) VALUES('"+array.get(2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(3)),2)+"','"+month+"');"+"\r\n")
                    .getBytes());
        }
        //out.close();
    }

    /**
     * 作者 LYC
     * 时间 2022/9/6
     * 参数 [list]
     * 返回值 void
     *方法描述：表20.签约渠道商户让利或返佣
     **/
    public static void readTable20(ArrayList<ArrayList<String>> list, FileOutputStream out, String month)throws IOException{
        out.write(("--table20\r\n").getBytes());
        for (int i = 0; i < list.size(); i++) {
            ArrayList<String> array=list.get(i);
            System.out.println("INSERT INTO KPI_TABLE20(STAFF_NAME,AMOUNT,MONTH) VALUES('"+array.get(2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(3)),2)+"','"+month+"');");
            out.write(("INSERT INTO KPI_TABLE20(STAFF_NAME,AMOUNT,MONTH) VALUES('"+array.get(2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(3)),2)+"','"+month+"');"+"\r\n")
                    .getBytes());
        }
        //out.close();
    }
    /**
     * 作者 LYC
     * 时间 2022/9/6
     * 参数 [list]
     * 返回值 void
     *方法描述：表21 其他
     **/
    public static void readTable21(ArrayList<ArrayList<String>> list, FileOutputStream out, String month)throws IOException{
        out.write(("--table21\r\n").getBytes());
        for (int i = 0; i < list.size(); i++) {
            ArrayList<String> array=list.get(i);
            System.out.println("INSERT INTO KPI_TABLE21(STAFF_NAME,VALUATION_AMT,OTHER_AMT,QUARTER_AMT,COMPLETION_RATE,SCORE,OTHER_AMT1,MONTH)" +
                    " VALUES('"+array.get(2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(3)),2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(4)),2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(5)),2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(6)),4)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(7)),2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(8)),2)+"','"+month+"');");
            out.write(("INSERT INTO KPI_TABLE21(STAFF_NAME,VALUATION_AMT,OTHER_AMT,QUARTER_AMT,COMPLETION_RATE,SCORE,OTHER_AMT1,MONTH)" +
                    " VALUES('"+array.get(2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(3)),2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(4)),2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(5)),2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(6)),4)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(7)),2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(8)),2)+"','"+month+"');"+"\r\n")
                    .getBytes());
        }
        //out.close();
    }

    /**
     * 作者 LYC
     * 时间 2022/9/7
     * 参数 [list]
     * 返回值 void
     *方法描述：表22 表22.有效终端维护
     **/
    public static void readTable22(ArrayList<ArrayList<String>> list, FileOutputStream out, String month) throws IOException{
        out.write(("--table22\r\n").getBytes());
        for (int i = 0; i < list.size(); i++) {
            ArrayList<String> array=list.get(i);
            System.out.println("INSERT INTO KPI_TABLE22(STAFF_NAME,TER_NUM,AMOUNT,MONTH) VALUES('"+array.get(0)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(1)),2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(2)),2)+"','"+month+"');");

            out.write(("INSERT INTO KPI_TABLE22(STAFF_NAME,TER_NUM,AMOUNT,MONTH) VALUES('"+array.get(0)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(1)),2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(2)),2)+"','"+month+"');"+"\r\n")
                            .getBytes());
        }
        //out.close();
    }
    /**
     * 作者 LYC
     * 时间 2022/9/7
     * 参数 [list]
     * 返回值 void
     *方法描述：表23.无效、低效终端撤机
     **/
    public static void readTable23(ArrayList<ArrayList<String>> list, FileOutputStream out, String month) throws IOException{
        out.write(("--table23\r\n").getBytes());
        for (int i = 0; i < list.size(); i++) {
            ArrayList<String> array=list.get(i);
            System.out.println("INSERT INTO KPI_TABLE23(STAFF_NAME,TER_NUM_CT,TER_NUM_ZN,TER_NUM_SUM,AMOUNT,MONTH) " +
                    "VALUES('"+array.get(1)+"','0','0','"+array.get(2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(3)),2)+"','"+month+"');");

            out.write(("INSERT INTO KPI_TABLE23(STAFF_NAME,TER_NUM_CT,TER_NUM_ZN,TER_NUM_SUM,AMOUNT,MONTH) " +
                    "VALUES('"+array.get(1)+"','0','0','"+array.get(2)+"'," +
                    "'"+DoubleUtil.round_toformat(Double.parseDouble(array.get(3)),2)+"','"+month+"');"+"\r\n")
                            .getBytes());
        }
        //out.close();

    }
    /**
     * 作者 LYC
     * 时间 2022/9/7
     * 参数 [list]
     * 返回值 void
     *方法描述：表24.商服、郊县服务薪点薪点
     **/
    public static void readTable24(ArrayList<ArrayList<String>> list, FileOutputStream out, String month) throws IOException {
        out.write(("--table24\r\n").getBytes());
        for (int i = 0; i < list.size(); i++) {
            ArrayList<String> array=list.get(i);
            System.out.println("INSERT INTO KPI_TABLE24(STAFF_NAME,TER_NUM,AMOUNT,MONTH) VALUES('"+array.get(3)+"','"+array.get(4)+"'," +
                    "'"+ DoubleUtil.round_toformat(Double.parseDouble(array.get(5)),2)+"','"+month+"');");
            out.write(("INSERT INTO KPI_TABLE24(STAFF_NAME,TER_NUM,AMOUNT,MONTH) VALUES('"+array.get(3)+"','"+array.get(4)+"'," +
                    "'"+ DoubleUtil.round_toformat(Double.parseDouble(array.get(5)),2)+"','"+month+"');"+"\r\n")
                            .getBytes());
        }
        //out.close();
    }


    public static void main(String[] args) {
        String newFile="F:\\bigdata\\temp\\3月绩效数据\\Ins2023031.sql";
        try{
            File newfile = new File(newFile);
            newfile.createNewFile();
            BufferedWriter out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(newfile), "UTF-8"));
            // readTable1(readFile("F:\\bigdata\\temp\\3月绩效数据\\表1.新拓展优质商户收单净收益奖励明细表-3月.xlsx").get("sheet0"),out);
            //readTable2(readFile("F:\\bigdata\\temp\\3月绩效数据\\表2.新拓展线上收单商户收单净收益奖励明细表-3月.xlsx").get("sheet0"),null);
            //readTable3(readFile("F:\\bigdata\\temp\\3月绩效数据\\表3.存量计提净收单收益商户收单净收益奖励明细表-3月.xlsx").get("sheet0"),null);
            // readTable4(readFile("F:\\bigdata\\temp\\3月绩效数据\\表4.每个客户经理当月收单净收益-3月.xlsx").get("sheet0"),null);
            //readTable5(readFile("F:\\bigdata\\temp\\3月绩效数据\\表5.存量月均净收单收益-2月.xlsx").get("sheet0"),null);
            //readTable7(readFile("F:\\bigdata\\temp\\3月绩效数据\\表7.产品计价奖励（3月）.xlsx").get("sheet0"),null);
            //  readTable8(readFile("F:\\bigdata\\temp\\3月绩效数据\\表8.营销联盟活动奖励（3月）.xlsx").get("sheet0"),null);
            //  readTable9(readFile("F:\\bigdata\\temp\\3月绩效数据\\表9.行业ERP、智慧园区(1月无).xlsx").get("sheet0"),null);
            //       readTable10(readFile("F:\\bigdata\\temp\\3月绩效数据\\表10-天天富融资理财-2023.1.xlsx").get("sheet0"),null);
            //readTable12(readFile("F:\\bigdata\\temp\\3月绩效数据\\表12.宝信返佣奖励（3月）.xlsx").get("sheet0"),null);
            // readTable14(readFile("F:\\bigdata\\temp\\3月绩效数据\\表14.行缴奖励（3月）.xlsx").get("sheet0"),null);
            //readTable20(readFile("F:\\bigdata\\temp\\3月绩效数据\\表20.签约渠道商户让利或返佣 - 3月.xlsx").get("sheet0"),null);
            // readTable21(readFile("F:\\bigdata\\temp\\3月绩效数据\\表21.3月其他.xlsx").get("sheet0"),null);
            // readTable22(readFile("F:\\bigdata\\temp\\3月绩效数据\\表22.有效终端维护-3月.xlsx").get("sheet0"),null);
            // readTable23(readFile("F:\\bigdata\\temp\\3月绩效数据\\表23.无效、低效终端清理撤机（3月）.xlsx").get("sheet0"),null);
//            readTable24(readFile("F:\\bigdata\\temp\\3月绩效数据\\表42.收款单奖励（3月）.xlsx").get("sheet0"),null);
        }catch (Exception e){
            e.printStackTrace();
        }

    }
}
